<?php

class ReporteController extends Controller
{
	public function actionDocumentoDepartamento()
	{
		$model=new Departamentos('search');
		$model->unsetAttributes();  // clear any default values
		if(isset($_GET['Departamentos']))
			$model->attributes=$_GET['Departamentos'];

		$this->render('DocumentoDepartamento',array(
			'model'=>$model,
		));
	}

	public function actionDocumentoFlujo()
	{  $model=new Flujos('search');
            $model->unsetAttributes();  // clear any default values
            if(isset($_GET['Flujos']))
                $model->attributes=$_GET['Flujos'];

            $this->render('DocumentoFlujo',array(
            'model'=>$model,
        ));
		
	}

	public function actionDocumentoPrioridad()
	{ 
            
            if(isset($_POST['fechaInicio'])&&isset($_POST['fechaFin']))
                {
                    Yii::app()->user->setState('fechaInicioPrioridad',$_POST['fechaInicio']);
                    Yii::app()->user->setState('fechaFinPrioridad',$_POST['fechaFin']);
                    if(isset($_POST['alta']))
                        {
                        $Prioridad='Alta';
                        Yii::app()->user->setState('Prioridad',$Prioridad);
                        }
                    else if (isset($_POST['media']))
                        {
                        $Prioridad='Media';
                        Yii::app()->user->setState('Prioridad',$Prioridad);
                    }
                    elseif($_POST['baja'])
                        {
                        $Prioridad='Baja';
                        Yii::app()->user->setState('Prioridad',$Prioridad);
                         }
                }
                
                $Inicio =Yii::app()->user->getState('fechaInicioPrioridad'); 
                $Fin =Yii::app()->user->getState('fechaFinPrioridad'); 
                $Prioridad =Yii::app()->user->getState('Prioridad');
                if(isset($Inicio) && isset($Fin) )
                {
                        $arrInicio = explode('/', $Inicio); 
			$fechaInicio = $arrInicio[2].'-'.$arrInicio[1].'-'.$arrInicio[0]; 
			
			$arrFin = explode('/',  $Fin); 
			$fechaFin = $arrFin[2].'-'.$arrFin[1].'-'.$arrFin[0]; 
			
			$connection = Yii::app()->db;
		
                        
           
                    
                        if($Prioridad=='Alta')
                        {
                        $sqlScript = "call reporte_general_prioridad('".$fechaInicio." 00:00:00','".$fechaFin." 23:59:59','Alta');";
                        }
                        else if($Prioridad=='Media'){                  
                           $sqlScript = "call reporte_general_prioridad('".$fechaInicio." 00:00:00','".$fechaFin." 23:59:59','Media');";
                        }
                        else if($Prioridad=='Baja'){
                        
                           $sqlScript = "call reporte_general_prioridad('".$fechaInicio." 00:00:00','".$fechaFin." 23:59:59','Baja');";
                        }
                        
                        $command = $connection->createCommand($sqlScript);
			
			$dataReader = $command->query();		
			
			$rows=$dataReader->readAll();				
			
			$connection->active=false;
			
			
			$dataProvider2=new CSqlDataProvider($sqlScript, array(
			    'totalItemCount'=>count($rows),
                            'keyField'=>'IdSistema',
			    'pagination'=>array(
			       'pageSize'=>10,
			    ),
			));
			
		$dataProvider = $dataProvider2->getData();
			$this->render('ResultadoDocumentoPrioridad',array('dataProvider'=>$dataProvider2,'Prioridad'=>$Prioridad));
                   
              
               }
               else
               {
		$this->render('DocumentoPrioridad');
               }
		
	}

	public function actionDocumentoUsuario()
	{
        $model=new Usuarios('search');
        $model->unsetAttributes();  // clear any default values
        if(isset($_GET['Usuarios']))
            $model->attributes=$_GET['Usuarios'];

        $this->render('DocumentoUsuario',array(
            'model'=>$model,
        ));
		
	}

	public function actionGeneral()
	{
            
		if(isset($_POST['fechaInicio'])&&isset($_POST['fechaFin']))
                {
                    Yii::app()->user->setState('fechaInicioGeneral',$_POST['fechaInicio']);
                    Yii::app()->user->setState('fechaFinGeneral',$_POST['fechaFin']);
                }
                
                $Inicio =Yii::app()->user->getState('fechaInicioGeneral'); 
                $Fin =Yii::app()->user->getState('fechaFinGeneral'); 
                
                if(isset($Inicio) && isset($Fin) )
                {
			$arrInicio = explode('/',$Inicio ); 
			$fechaInicio = $arrInicio[2].'-'.$arrInicio[1].'-'.$arrInicio[0]; 
			
			$arrFin = explode('/',  $Fin); 
			$fechaFin = $arrFin[2].'-'.$arrFin[1].'-'.$arrFin[0]; 
			
								

                $count=Yii::app()->db->createCommand(" 
select count(d.id) as IdSistema
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition'")->queryScalar();

    
                $sql=        " 
select d.id as IdSistema,
    value as NumeroTramite,
(select value from document_fields_link where document_field_id=11 and metadata_version_id= d.id) as Prioridad,       
d.created as fechaIngreso,
(select value from document_fields_link where document_field_id=15 and metadata_version_id= d.id) as Terminado,
    u.username as Usuario,
    
    w.name as tramite,
    groups_lookup.name as Departamento,
(select value from document_fields_link where document_field_id=2 and metadata_version_id= d.metadata_version_id ) as asunto,
    (SELECT doct.datetime FROM documents doc inner join document_transactions doct on doc.id = doct.document_id where doct.transaction_namespace='ktcore.transactions.workflow_state_transition' order by doct.datetime desc limit 1) as FechaResolucion,
    
     WS.name AS EstadoTramite,    
    (select value from document_fields_link where document_field_id=10 and metadata_version_id= d.id) as LeyTransparencia,
    dt2.comment as ComentarioTramite
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition'
    group by IdSistema,NumeroTramite,fechaIngreso,Usuario,tramite,asunto,FechaResolucion,EstadoTramite,LeyTransparencia,ComentarioTramite                            ";


                	
                        $dataProvider=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'IdSistema',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
                        
			
			
                        //$dataProvider = $dataProvider2->getData();
			$this->render('ResultadoGeneral',array('dataProvider'=>$dataProvider));
                    }
                    else
                    {
           
                    $this->render('General');
                    }
	}

	public function actionIndex()
	{
		$this->render('index');
	}
        
        
        public function actionBorrar()
                {
           
                  Yii::app()->session->clear();
                  $this->render('index');
                   
                   
                
                }
        
        public function actionFecha()
       {
            
          
            
            if(isset($_POST['fechaInicio'])&&isset($_POST['fechaFin'])&&isset($_POST['id'])&&isset($_POST['tipo']))
                {
                Yii::app()->user->setState('fechaInicioFecha',$_POST['fechaInicio']);
                Yii::app()->user->setState('fechaFinFecha',$_POST['fechaFin']);
                Yii::app()->user->setState('idFecha',$_POST['id']);
                Yii::app()->user->setState('tipoFecha',$_POST['tipo']);
                
                }
                $Inicio =Yii::app()->user->getState('fechaInicioFecha'); 
                $Fin =Yii::app()->user->getState('fechaFinFecha'); 
                $Id =Yii::app()->user->getState('idFecha'); 
                $Tipo =Yii::app()->user->getState('tipoFecha'); 
                
                if(isset($Inicio)&&isset($Fin)&&isset($Id)&&isset($Tipo))
                {
                    
                
                 $arrInicio = explode('/', $Inicio); 
			$fechaInicio = $arrInicio[2].'-'.$arrInicio[1].'-'.$arrInicio[0]; 
			
			$arrFin = explode('/', $Fin); 
			$fechaFin = $arrFin[2].'-'.$arrFin[1].'-'.$arrFin[0]; 
			
			$connection = Yii::app()->db;
                    if($Tipo == 'Departamento')
                        {
                        
                        $count=Yii::app()->db->createCommand("select count(d.id) as IdSistema
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
   where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and groups_lookup.id = $Id
                        
")->queryScalar();
                        
                        
                            $sql ="select d.id as IdSistema,
    value as NumeroTramite,
        (select value from document_fields_link where document_field_id=11 and metadata_version_id= d.id) as Prioridad, 
     '' as Interesado,    
    d.created as fechaIngreso,
    (select value from document_fields_link where document_field_id=15 and metadata_version_id= d.id) as Terminado,
    u.username as Usuario,
    w.name as tramite,
    groups_lookup.name as Departamento,
    (select value from document_fields_link where document_field_id=2 and metadata_version_id= d.metadata_version_id ) as asunto,
     '' as MemoResolucion,
    '' as DepartamentoResolucion,
    (SELECT doct.datetime FROM documents doc inner join document_transactions doct on doc.id = doct.document_id where doct.transaction_namespace='ktcore.transactions.workflow_state_transition' order by doct.datetime desc limit 1) as FechaResolucion,
    
     WS.name AS EstadoTramite,    
    (select value from document_fields_link where document_field_id=10 and metadata_version_id= d.id) as LeyTransparencia,
    dt2.comment as ComentarioTramite
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id   
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and groups_lookup.id = $Id
    group by IdSistema,NumeroTramite,Interesado,fechaIngreso,Usuario,groups_lookup.name,tramite,asunto,MemoResolucion,DepartamentoResolucion,FechaResolucion,EstadoTramite,LeyTransparencia,ComentarioTramite";
                    
                     $dataProvider=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'IdSistema',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
			
                        //$dataProvider = $dataProvider->getData();
			$this->render('ResultadoDocumentoDepartamento',array('dataProvider'=>$dataProvider,'Id'=>$Id,'Tipo'=>$Tipo));
                   
              
                             
                        }
                    else if($Tipo == 'Usuario')
                        {
                        $sql = "select d.id as IdSistema,
    value as NumeroTramite,
   (select value from document_fields_link where document_field_id=11 and metadata_version_id= d.id) as Prioridad, 
    d.created as fechaIngreso,
    (select value from document_fields_link where document_field_id=15 and metadata_version_id= d.id) as Terminado,
    u.username as Usuario,
    w.name as tramite,
    groups_lookup.name as Departamento,
    (select value from document_fields_link where document_field_id=2 and metadata_version_id= d.metadata_version_id ) as asunto,
    (SELECT doct.datetime FROM documents doc inner join document_transactions doct on doc.id = doct.document_id where doct.transaction_namespace='ktcore.transactions.workflow_state_transition' order by doct.datetime desc limit 1) as FechaResolucion,
     WS.name AS EstadoTramite,    
    (select value from document_fields_link where document_field_id=10 and metadata_version_id= d.id) as LeyTransparencia,
    dt2.comment as ComentarioTramite
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id 
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id        
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id

    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and u.id = $Id
    group by IdSistema,NumeroTramite,fechaIngreso,Usuario,groups_lookup.name,tramite,asunto,FechaResolucion,EstadoTramite,LeyTransparencia,ComentarioTramite ";
                        
      $count=Yii::app()->db->createCommand("select COUNT(d.id) as IdSistema
    from documents as d
    inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and u.id = $Id")->queryScalar();
                        
      $dataProvider=new CSqlDataProvider($sql, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'IdSistema',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
			
			
			
              
			$this->render('ResultadoDocumentoUsuario',array('dataProvider'=>$dataProvider,'Id'=>$Id,'Tipo'=>$Tipo));
                   
              
                        
                        }
                        else if($Tipo == 'Flujo')
                            {
                            if(isset($_POST['name']))
                            {
                            Yii::app()->user->setState('nameFecha',$_POST['name']);
                            }
                            $name =Yii::app()->user->getState('nameFecha');
                            $count=Yii::app()->db->createCommand("select COUNT(d.id) as IdSistema
    from documents as d
     inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and WS.name = '$name'
   ")->queryScalar();
                            
                            
                            
                            $sqlScript = "select d.id as IdSistema,
    value as NumeroTramite,
    (select value from document_fields_link where document_field_id=11 and metadata_version_id= d.id) as Prioridad,    
    d.created as fechaIngreso,
    (select value from document_fields_link where document_field_id=15 and metadata_version_id= d.id) as Terminado,
    u.username as Usuario,
    w.name as tramite,
    groups_lookup.name as Departamento,
    (select value from document_fields_link where document_field_id=2 and metadata_version_id= d.metadata_version_id ) as asunto,
    (SELECT doct.datetime FROM documents doc inner join document_transactions doct on doc.id = doct.document_id where doct.transaction_namespace='ktcore.transactions.workflow_state_transition' order by doct.datetime desc limit 1) as FechaResolucion,
    
     WS.name AS EstadoTramite,    
    (select value from document_fields_link where document_field_id=10 and metadata_version_id= d.id) as LeyTransparencia,
    dt2.comment as ComentarioTramite
    from documents as d
     inner join document_transactions dt2 on d.id = dt2.document_id
    inner join users as u on dt2.user_id = u.id
    inner join users_groups_link on u.id = users_groups_link.user_id
    inner join groups_lookup on users_groups_link.group_id = groups_lookup.id
    inner join document_fields_link dfl on d.metadata_version_id = dfl.metadata_version_id  
    inner join document_fields df on dfl.document_field_id = df.id
    inner join document_metadata_version dmv on dmv.id = d.metadata_version_id
    inner join workflows w on dmv.workflow_id = w.id  
    LEFT JOIN document_metadata_version AS DMVER ON d.metadata_version_id = DMVER.id 
    INNER JOIN workflow_states AS WS ON WS.id = DMVER.workflow_state_id
    where d.created between '$fechaInicio 00:00:00' and '$fechaFin 23:59:59' and df.id = 8  and d.metadata_version_id= dfl.metadata_version_id and dt2.transaction_namespace='ktcore.transactions.workflow_state_transition' and WS.name = '$name'
    group by IdSistema,NumeroTramite,fechaIngreso,Usuario,tramite,asunto,FechaResolucion,EstadoTramite,LeyTransparencia,ComentarioTramite 

                            ";
                            
			
			 $dataProvider=new CSqlDataProvider($sqlScript, array(
                    'totalItemCount'=>$count,
                    'keyField'=>'IdSistema',
                    /*'sort'=>array(
                        'attributes'=>array(
                             'created','creator_id', 'name', 'value',
                        ),
                    ),*/
                    'pagination'=>array(
                        'pageSize'=>10,
                    ),
                ));
			$this->render('ResultadoDocumentoFlujo',array('dataProvider'=>$dataProvider,'Id'=>$Id,'Tipo'=>$Tipo,'Name'=>$name));
                            }
               
                
                }
                else if(isset($_GET['id']))
                {
                    if(isset($_GET['name']))
                        {
                        $this->render('Fecha',array('id'=>$_GET['id'],'tipo'=>$_GET['tipo'],'name'=>$_GET['name']));
                        }
                        else{
                    $this->render('Fecha',array('id'=>$_GET['id'],'tipo'=>$_GET['tipo']));
                        }
                }
               
          
                
            
       }

	// Uncomment the following methods and override them if needed
	
}